﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RRHH.Entities;
using System.Data.SqlClient;
using System.Data;

namespace RRHH.DataAccess
{
    public class DAOUbigeo
    {
        public List<BEDepartamento> ListDepartamento()
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append(" SELECT IDDEPARTAMENTO, DESCRIPCION  FROM DEPARTAMENTO");
            string sql = sqlBuilder.ToString();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sql;
            using (DataAccess adoDataService = new DataAccess())
            {
                List<BEDepartamento> listDep = new List<BEDepartamento>();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                while (dr.Read())
                {
                    BEDepartamento oDepartamento = new BEDepartamento();
                    oDepartamento.IDDEPARTAMENTO = Convert.ToInt32(dr["IDDEPARTAMENTO"]);
                    oDepartamento.DESCRIPCION = Convert.ToString(dr["DESCRIPCION"]);

                    listDep.Add(oDepartamento);
                }

                dr.Close();
                adoDataService.Dispose();
                return listDep;
            }
        }

        public List<BEProvincia> ListProvincia(int idDep)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT IDDEPARTAMENTO,IDPROVINCIA,DESCRIPCION FROM PROVINCIA WHERE IDDEPARTAMENTO=" + idDep);
            string sql = sqlBuilder.ToString();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sql;
            using (DataAccess adoDataService = new DataAccess())
            {
                List<BEProvincia> listProv = new List<BEProvincia>();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                while (dr.Read())
                {
                    BEProvincia oProvincia = new BEProvincia();
                    oProvincia.IDPROVINCIA = Convert.ToInt32(dr["IDPROVINCIA"]);
                    oProvincia.DESCRIPCION = Convert.ToString(dr["DESCRIPCION"]);

                    listProv.Add(oProvincia);
                }

                dr.Close();
                adoDataService.Dispose();
                return listProv;
            }
        }

        public List<BEDistrito> ListDistrito(int idDep, int idProv)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT IDDEPARTAMENTO,IDPROVINCIA,IDDISTRITO,DESCRIPCION FROM DISTRITO WHERE IDDEPARTAMENTO= " + idDep +"  AND IDPROVINCIA=" + idProv);
            string sql = sqlBuilder.ToString();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sql;
            using (DataAccess adoDataService = new DataAccess())
            {
                List<BEDistrito> listDep = new List<BEDistrito>();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                while (dr.Read())
                {
                    BEDistrito oDistrito = new BEDistrito();
                    oDistrito.IDDISTRITO = Convert.ToInt32(dr["IDDISTRITO"]);
                    oDistrito.DESCRIPCION = Convert.ToString(dr["DESCRIPCION"]);
                    listDep.Add(oDistrito);
                }

                dr.Close();
                adoDataService.Dispose();
                return listDep;
            }
        }




    }
}
